package com.mhd.databases.service;

import com.mhd.databases.common.ResultEnum;
import com.mhd.databases.config.DbConfig;
import com.mhd.databases.pojo.DbInfo;
import com.mhd.databases.pojo.TableInfo;
import com.mhd.databases.util.CsvUtils;
import com.mhd.databases.util.JdbcUtils;
import com.mhd.databases.util.ReadFileUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author dmh96
 */
@Service
public class TestServiceImpl implements TestService {

    private static final Logger logger = LoggerFactory.getLogger(TestServiceImpl.class);

    @Autowired
    DbConfig dbConfig;

    @Value("${export.csv.path}")
    private String exportCsvPath;

    @Override
    public String test() {
        logger.info(dbConfig.toString());
        /**
         * 判断配置文件为空情况
         */
        boolean isNull = dbConfig == null || (dbConfig.getOracle() == null && dbConfig.getMysql() == null);
        if (isNull) {
            ResultEnum. FAILED.setMsg("数据库连接信息为空");
            return ResultEnum.FAILED.getCode();
        }
        /**
         * 扫描oracle数据库表结构信息
         */
        List<DbInfo> oracleDbs = dbConfig.getOracle();
        String oracleSql = ReadFileUtils.readSql(ReadFileUtils.ORACLE_URL);
//		String oracleSql =
//				"SELECT\n"
//						+ "\tt1.TABLE_NAME AS \"tableName\",\n"
//						+ "\tNVL( BYTES, 0 ) AS \"bytes\",\n"
//						+ "\tCREATED AS \"createTime\",\n"
//						+ "\tt4.COMMENTS AS \"tableComments\",\n"
//						+ "\tt5.column_name AS \"columnName\",\n"
//						+ "\tt5.data_type AS \"columType\",\n"
//						+ "CASE\n"
//						+ "\t\tt5.nullable \n"
//						+ "\t\tWHEN 'N' THEN\n"
//						+ "\t\t'否' ELSE '是' \n"
//						+ "\tEND AS \"isNullable\",\n"
//						+ "\tt6.comments AS \"columComments\",\n"
//						+ "CASE\n"
//						+ "\t\tt7.constraint_type \n"
//						+ "\t\tWHEN 'P' THEN\n"
//						+ "\t\t'是' ELSE '否' \n"
//						+ "\tEND AS \"isPrimaryKey\" \n"
//						+ "FROM\n"
//						+ "\t( SELECT TABLE_NAME FROM USER_TABLES ) t1\n"
//						+ "\tLEFT JOIN ( SELECT SEGMENT_NAME, BYTES FROM USER_SEGMENTS ) t2 ON t2.SEGMENT_NAME = t1.TABLE_NAME\n"
//						+ "\tLEFT JOIN ( SELECT OBJECT_NAME, CREATED FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'TABLE' ) t3 ON t3.OBJECT_NAME = t1.TABLE_NAME\n"
//						+ "\tLEFT JOIN ( SELECT TABLE_NAME, COMMENTS FROM user_tab_comments WHERE TABLE_TYPE = 'TABLE' ) t4 ON t4.TABLE_NAME = t1.TABLE_NAME\n"
//						+ "\tLEFT JOIN ( SELECT table_name, column_name, data_type, nullable FROM user_tab_cols ) t5 ON t5.table_name = t1.table_name\n"
//						+ "\tLEFT JOIN ( SELECT table_name, column_name, comments FROM user_col_comments ) t6 ON t6.column_name = t5.column_name \n"
//						+ "\tAND t6.table_name = t1.table_name\n"
//						+ "\tLEFT JOIN (\n"
//						+ "\tSELECT\n"
//						+ "\t\tcol.column_name,\n"
//						+ "\t\tcon.constraint_type,\n"
//						+ "\t\tcol.table_name \n"
//						+ "\tFROM\n"
//						+ "\t\tuser_constraints con,\n"
//						+ "\t\tuser_cons_columns col \n"
//						+ "\tWHERE\n"
//						+ "\t\tcon.constraint_name = col.constraint_name \n"
//						+ "\t\tAND con.constraint_type = 'P' \n"
//						+ "\t) t7 ON t7.column_name = t5.column_name \n"
//						+ "\tAND t7.table_name = t1.table_name\n"
//						+ "\tORDER BY \"tableName\"";
        if (oracleDbs != null && !oracleDbs.isEmpty()) {
            for (DbInfo oracleDb : oracleDbs) {
                List<TableInfo> resLists =
                        JdbcUtils.connectDbToSearch(
                                JdbcUtils.ORACLE,
                                oracleDb.getUrl(),
                                oracleDb.getUsername(),
                                oracleDb.getPassword(),
                                oracleSql);
                if(resLists == null || resLists.isEmpty()){
                    ResultEnum.FAILED.setMsg("数据库连接异常");
                    return ResultEnum.FAILED.getCode();
                }
                String resCsv = getTableInfoToCsv(resLists, JdbcUtils.ORACLE);
                if(ResultEnum.FAILED.getCode().equalsIgnoreCase(resCsv)){
                    ResultEnum.FAILED.setMsg("数据导出csv文件异常");
                    return ResultEnum.FAILED.getCode();
                }
            }
        }
        /**
         * 扫描mysql数据库表结构信息
         */
        List<DbInfo> mysqlDbs = dbConfig.getMysql();
        if (mysqlDbs != null && !mysqlDbs.isEmpty()) {
            for (DbInfo mysqlDb : mysqlDbs) {
                String url = mysqlDb.getUrl();
                String[] split = url.split("\\?")[0].split("/");
                String dbName = split[split.length - 1];
                String mysqlSql = ReadFileUtils.readSql(ReadFileUtils.MYSQL_URL) + dbName + "'";
    //      String mysqlSql =
    //          "SELECT\n"
    //              + "\tt.TABLE_NAME AS tableName,\n"
    //              + "t.DATA_LENGTH AS bytes,\n"
    //              + "t.CREATE_TIME AS createTime,\n"
    //              + "\tt.TABLE_COMMENT AS tableComments,\n"
    //              + "\tc.COLUMN_NAME AS columnName,\n"
    //              + "\tc.DATA_TYPE AS columType,\n"
    //              + "\t(CASE c.IS_NULLABLE\n"
    //              + "\tWHEN 'NO' THEN\n"
    //              + "\t\t'否'\n"
    //              + "\tELSE\n"
    //              + "\t\t'是'\n"
    //              + "END) AS isNullable,\n"
    //              + "c.IS_NULLABLE,\n"
    //              + "\tc.COLUMN_COMMENT AS columComments,\n"
    //              + "\tc.COLUMN_KEY,\n"
    //              + "\t(CASE c.COLUMN_KEY\n"
    //              + "\tWHEN 'PRI' THEN\n"
    //              + "\t\t'是'\n"
    //              + "\tELSE\n"
    //              + "\t\t'否'\n"
    //              + "END) AS isPrimaryKey\n"
    //              + "FROM\n"
    //              + "\tinformation_schema.`TABLES` t\n"
    //              + "\tLEFT JOIN information_schema.`COLUMNS` c ON c.TABLE_NAME=t.TABLE_NAME\n"
    //              + "\tAND c.table_schema = t.table_schema\n"
    //              + "WHERE\n"
    //              + "\tt.table_schema = '"
    //              + dbName
    //              + "'\n" /*TODO mysql数据库名要置换为动态*/
    //              + "\tORDER BY tableName";
                List<TableInfo> resLists =
                        JdbcUtils.connectDbToSearch(
                                JdbcUtils.MYSQL,
                                mysqlDb.getUrl(),
                                mysqlDb.getUsername(),
                                mysqlDb.getPassword(),
                                mysqlSql);
                if(resLists == null || resLists.isEmpty()){
                    ResultEnum.FAILED.setMsg("数据库连接异常");
                    return ResultEnum.FAILED.getCode();
                }

                String resCsv = getTableInfoToCsv(resLists, JdbcUtils.MYSQL);
                if(ResultEnum.FAILED.getCode().equalsIgnoreCase(resCsv)){
                    ResultEnum.FAILED.setMsg("数据导出csv文件异常");
                    return ResultEnum.FAILED.getCode();
                }
            }
        }

        return ResultEnum.SUCCESS.getCode();
    }

    private String getTableInfoToCsv(List<TableInfo> resLists, String dbType) {
        Map<String, List<TableInfo>> map = new HashMap<>();
        for (TableInfo resList : resLists) {
            logger.info("---list" + resList.toString());
            String tableName = resList.getTableName();
            if (map.containsKey(tableName)) {
                map.get(tableName).add(resList);
            } else {
                List<TableInfo> tableInfos = new ArrayList<>();
                tableInfos.add(resList);
                map.put(tableName, tableInfos);
            }
        }
        logger.info("---map" + map);
        for (String key : map.keySet()) {
            logger.info(map.get(key).toString());
            String res = CsvUtils.exportCsv(map.get(key), /*"D:\\temp\\"*/ exportCsvPath + dbType + "\\" + key + ".csv");
            if(ResultEnum.FAILED.getCode().equalsIgnoreCase(res)){
                return ResultEnum.FAILED.getCode();
            }
        }
        return ResultEnum.SUCCESS.getCode();
    }
}
